﻿create PROC	usp_PhatSinhMa
	@TENBANG NVARCHAR(20),
	@NEW NVARCHAR(10) output
AS
	BEGIN
	--Khai bao bien
		DECLARE @STRSQL NVARCHAR(3000)
		DECLARE @OLD_id VARCHAR(10)
		DECLARE @TENTHUOCTINH VARCHAR(30)
		DECLARE @I INT=1
	--Lay ki tu viet tat
	DECLARE @VT VARCHAR(5)
	SET @VT = CASE @TENBANG 
			WHEN 'KHACHHANG' THEN 'KH'
			WHEN 'CHUYENXE' THEN 'CX'
			
			END
	--LẤY TÊN THUỘC TÍNH	
	DECLARE @PAR NVARCHAR(1000) = '@TENTT NVARCHAR(30) OUTPUT '
	SET @STRSQL = 'SELECT @TENTT = Col.Column_Name 
	FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
	WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = ''PRIMARY KEY''
    AND Col.Table_Name = ''' + @TENBANG +''''
    
    EXECUTE sp_EXECuteSQL @STRSQL,@PAR,@TENTT = @TENTHUOCTINH OUTPUT
    --LẤY MÃ CŨ
	SET @PAR = '@OLD NVARCHAR(10) OUTPUT'
	SET @STRSQL = 'SELECT @OLD = MAX(' + @TENTHUOCTINH + ') FROM ' + @TENBANG 
	EXECUTE sp_EXECuteSQL @STRSQL,@PAR,@OLD = @OLD_ID OUTPUT
	--TÌM MÃ MỚI
	SET @I = RIGHT(@OLD_id, 10-LEN(@VT)) + 1
	SET @NEW = @VT + REPLICATE('0',10-(LEN(@VT)+LEN(@I))) + CAST(@I AS VARCHAR(10))
END

declare @NEW nvarchar(8)
exec usp_PhatSinhMa 'CHUYENXE',@NEW out
print(@NEW)

